﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using System.Data;
//using MySql.Data.MySqlClient;

namespace KPIS.GERP.GBMS.DAL
{
    [Serializable]
    public class BGMKPIResultListDAL : IBGMKPIResultListDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        //MySqlConnection conn = new MySqlConnection(SystemInfo._ConnectionString);

        public int LoadYearType()
        {
            int yearStart = 1;
            string strSQL;

            try
            {
                dbManager.Open();

                strSQL = "SELECT month_start FROM sys_year_type WHERE isactive  = 1";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                yearStart = Convert.ToInt32(dtb.Rows[0][0]);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
            return yearStart;
        }

        public List<MODEL.BGMKPI> LoadGridKpiTarget(int intTargetSeq)
        {
            int yearStart = LoadYearType();
            List<MODEL.BGMKPI> list = new List<BGMKPI>();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@target_seq", intTargetSeq);

                strSQL = "select * FROM bgm_kpi_target_detail  WHERE  target_seq = @target_seq";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                int inti = 1;

                foreach (DataRow r in dtb.Rows)
                {
                    BGMKPI bgmkpi = new BGMKPI();
                    bgmkpi.TargetSeq = Convert.ToInt32(r["target_det_seq"]);
                    bgmkpi.Seq = Convert.ToInt32(r["kpi_det_seq"]);
                    bgmkpi.KpiTarget = Convert.ToString(r["target_name"]);
                    bgmkpi.Score = 0;
                    bgmkpi.Order = inti++;

                    list.Add(bgmkpi);
                }

                var queryAfter10 = from kpi in list where kpi.Order >= yearStart select kpi;
                var queryBefore10 = from kpi in list where kpi.Order < yearStart select kpi;
                var queryComplete = queryAfter10.Union(queryBefore10);
                list = queryComplete.ToList();

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return list;
        }

        public DataTable SearchTarget(int intKpiSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@kpi_seq", intKpiSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT bkd.kpi_det_seq, bkd.target_det_seq AS TargetSeq, kdl.target_name AS KpiTarget,"
                          + " bkd.kpi_det_target AS Target, bkd.kpi_det_result AS Result FROM bgm_kpi_detail bkd "
                          + " LEFT JOIN bgm_kpi_header bkh ON bkh.kpi_seq = bkd.kpi_seq"
                          + " LEFT JOIN bgm_kpi_target_detail kdl ON bkd.target_det_seq = kdl.target_det_seq"
                          + " WHERE bkh.kpi_seq = @kpi_seq  AND bkh.record_status not like @status";

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListJobsiteKPI()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " select job_site_seq as value, job_site_name as text"
                             + " from hrm_mp_job_sites"
                             + " where job_site_level_seq=3 AND record_status not like @status"
                             + " AND job_site_type_seq = 2 ";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListBgmProjSeq(int intYear, int intJobsite, int intProjectType)
        {
            try
            {
                //conn.Open();
                //MySqlCommand command = new MySqlCommand("bg_sp_kpi_result_project_sel", conn);
                //command.CommandType = CommandType.StoredProcedure;
                //command.Parameters.AddWithValue("intYear", intYear);
                //command.Parameters.AddWithValue("intJobsite", intJobsite);
                //command.Parameters.AddWithValue("intProjectType", intProjectType);
                //command.Parameters.AddWithValue("vchrStatus", SystemInfo._DeleteRecordStatus);

                DataTable dt = new DataTable();
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@intYear", intYear);
                dbManager.AddParameters(1, "@intJobsite", intJobsite);
                dbManager.AddParameters(2, "@intProjectType", intProjectType);
                dbManager.AddParameters(3, "@vchrStatus", SystemInfo._DeleteRecordStatus);

                //MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                //adapter.Fill(dt);
                string strSQL = "bg_sp_kpi_result_project_sel @intYear, @intJobsite, @intProjectType, @vchrStatus";
                dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public DataTable ListSubActKPI(int intYear)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_year", intYear);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT a.bgm_strat_data_map_seq, a.bgm_year, b.bgm_strat_list_seq, "
                             + " b.bgm_strat_list_code, b.bgm_strat_list_name, b.bgm_strat_type_seq "
                             + " FROM bgm_strategy_data_mapping a"
                             + " INNER JOIN bgm_strategy_data_list b ON b.bgm_strat_list_seq = a.bgm_strat_list_seq "
                             + " WHERE bgm_strat_type_seq='11' "
                             + " AND a.bgm_year= @bgm_year "
                             + " AND b.record_status not like @status ";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        
        public DataTable ListMainActKPI(int intYear)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_year", intYear);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT a.bgm_strat_data_map_seq, a.bgm_year, b.bgm_strat_list_seq, "
                             + " b.bgm_strat_list_code, b.bgm_strat_list_name, b.bgm_strat_type_seq "
                             + " FROM bgm_strategy_data_mapping a"
                             + " INNER JOIN bgm_strategy_data_list b ON b.bgm_strat_list_seq = a.bgm_strat_list_seq "
                             + " WHERE bgm_strat_type_seq='10' "
                             + " AND a.bgm_year= @bgm_year "
                             + " AND b.record_status not like @status ";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
                
        public DataTable ListOutputKPI(int intYear)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@bgm_year", intYear);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT a.bgm_strat_data_map_seq, a.bgm_year, b.bgm_strat_list_seq, "
                             + " b.bgm_strat_list_code, b.bgm_strat_list_name, b.bgm_strat_type_seq "
                             + " FROM bgm_strategy_data_mapping a"
                             + " INNER JOIN bgm_strategy_data_list b ON b.bgm_strat_list_seq = a.bgm_strat_list_seq "
                             + " WHERE bgm_strat_type_seq='9' "
                             + " AND a.bgm_year= @bgm_year "
                             + " AND b.record_status not like @status ";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable ListTargetKPI()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT target_seq, target_name "
                             + " FROM bgm_kpi_target "
                             + " WHERE isactive=1"
                             + " AND record_status not like @status ";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable LoadKPIListType()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT * "
                             + " FROM bgm_kpi_list_type "
                             + " WHERE isactive=1"
                              + " AND record_status not like @status ";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GridProject(int nStratType, int nTargetType, int nProjectID)
        {
            try
            {
                
                //conn.Open();
                //MySqlCommand command = new MySqlCommand("bg_sp_kpi_result_sel", conn);
                //command.CommandType = CommandType.StoredProcedure;
                //command.Parameters.AddWithValue("intStratType", nStratType);
                //command.Parameters.AddWithValue("intTargetType", nTargetType);
                //command.Parameters.AddWithValue("intProjectID", nProjectID);
                //command.Parameters.AddWithValue("vchrStatus", SystemInfo._DeleteRecordStatus);
                
                DataTable dt = new DataTable();
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@intStratType", nStratType);
                dbManager.AddParameters(1, "@intTargetType", nTargetType);
                dbManager.AddParameters(2, "@intProjectID", nProjectID);
                dbManager.AddParameters(3, "@vchrStatus", SystemInfo._DeleteRecordStatus);

                //MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                //adapter.Fill(dt);
                string strSQL = "bg_sp_kpi_result_sel @intStratType, @intTargetType, @intProjectID, @vchrStatus";
                dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                //MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                //adapter.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public DataTable GetProject(int intKpiSeq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@kpi_seq", intKpiSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);


                string sql = " SELECT *"
                             + " FROM bgm_kpi_header"
                             + " WHERE kpi_seq= @kpi_seq "
                             + " AND record_status not like @status ";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetKPIListType()
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " SELECT bgm_kpi_type_seq,bgm_kpi_type_name "
                             + " FROM bgm_mas_kpi_type "
                             + " WHERE record_status not like @status ";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetData(int intKpiSeq, int intTargetDetSeq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@kpi_seq", intKpiSeq);
                dbManager.AddParameters(1, "@target_det_seq", intTargetDetSeq);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);
                string sql = " SELECT kpi_seq, target_det_seq, kpi_det_target, kpi_det_result  "
                             + " FROM bgm_kpi_detail "
                             + " WHERE kpi_seq = @kpi_seq "
                             + " AND target_det_seq = @target_det_seq"
                             + " AND record_status not like @status ";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetName(int intKpiSeq, int intStratListSeq, int intKPIListTypeSeq)
        {
            try
            {
                //conn.Open();
                //MySqlCommand command = new MySqlCommand("bg_sp_kpi_strategy_name_sel", conn);
                //command.CommandType = CommandType.StoredProcedure;
                //command.Parameters.AddWithValue("intKpiSeq", intKpiSeq);
                //command.Parameters.AddWithValue("intStratListSeq", intStratListSeq);
                //command.Parameters.AddWithValue("intKPIListTypeSeq", intKPIListTypeSeq);
                //command.Parameters.AddWithValue("vchrStatus", SystemInfo._DeleteRecordStatus);

                DataTable dt = new DataTable();
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@intKpiSeq", intKpiSeq);
                dbManager.AddParameters(1, "@intStratListSeq", intStratListSeq);
                dbManager.AddParameters(2, "@intKPIListTypeSeq", intKPIListTypeSeq);
                dbManager.AddParameters(3, "@vchrStatus", SystemInfo._DeleteRecordStatus);

                string strSQL = "bg_sp_kpi_strategy_name_sel @intKpiSeq, @intStratListSeq, @intKPIListTypeSeq, @vchrStatus";
                dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                //MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                //adapter.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Close();
            }
        }

        public void KPIUpdate(int intKpiSeq, string strKpiName, string strDescription, int intKpiTypeSeq, 
                              string strKpiUnit, Nullable<int> intWeight)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@kpi_seq", intKpiSeq);
                dbManager.AddParameters(1, "@kpi_name", strKpiName);
                dbManager.AddParameters(2, "@description", strDescription);
                dbManager.AddParameters(3, "@kpi_type_seq", intKpiTypeSeq);
                dbManager.AddParameters(4, "@kpi_unit", strKpiUnit);
                dbManager.AddParameters(5, "@weight", intWeight);
                dbManager.AddParameters(6, "@status", SystemInfo._DeleteRecordStatus);

                string sql = " UPDATE bgm_kpi_header "
                             + " SET kpi_name=@kpi_name, description=@description, kpi_type_seq=@kpi_type_seq,"
                             + " kpi_unit=@kpi_unit, weight=@weight "
                             + " WHERE kpi_seq= @kpi_seq "
                             + " AND record_status not like @status ";


                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateTarget(int strKpiDetSeq, double? dblKpiDetResult, string created_by)
        {
            string strSQL = String.Empty;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@kpi_det_seq", strKpiDetSeq);
                dbManager.AddParameters(1, "@updated_by", created_by);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(3, "@kpi_det_result", dblKpiDetResult);

                strSQL = " UPDATE bgm_kpi_detail "
                         + " SET kpi_det_result = @kpi_det_result, "
                         + " updated_by = @updated_by, "
                         + " updated_when = GetDate() "
                         + " WHERE kpi_det_seq = @kpi_det_seq AND record_status not like @status";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

    }
}
